<?

final class sql{
	const get_all_links = "select * from t_links;";
	const get_page_info = 'select * from t_links where id=%d';
	const find_user = "select * from t_users where login='%s'";
	const get_menu_links = '
		select 
			l.* 
		from 
			t_links l, t_links_groups lg, t_links_members lm
		where
			lm.fk_group=lg.id and lm.fk_link=l.id and lg.id=%d
		order by
			l.priority
	';
	const update_info = "update %s set %s where %s='%s'";
	const insert_log = "insert into t_log values (null, %d, '%s', '%s', '%s')";
	
	// Log
	const get_log_count = "select count(*) from t_log";
	const get_logs = "select * from t_log order by time desc limit %d, %d";

	//Session
	const get_firstpage_id = '
		select 
			l.id
		from 
			t_links l, t_links_groups lg, t_links_members lm
		where
			lm.fk_group=lg.id and lm.fk_link=l.id and lg.id=%d and l.priority>=0
		order by
			l.priority asc
	';
	const get_ratelimit_by_user2 = "
		select
			*
		from
			t_users_ratelimits ur, t_ratelimits rl
		where
			ur.fk_ratelimit=rl.id and ur.fk_user=%d
	";

	// FirstPage
	const get_sessions = "select * from t_sessions s where s.`end_time`>=%d";
	const get_sessions2 = "
select
	u.login,uf.ip,max(s.id) as id,u.id as user_id
from
	t_users u, t_user_from uf, t_sessions s
where
	uf.fk_user=u.id and s.fk_user=u.id and
    s.end_time>=%d
group by
	u.login
	";
	const get_session_info = "select * from t_sessions s where s.id=%d";
	const get_balance_last_update = "select max(b.last_update) from t_bills b";
	const get_bills_month = "
select 
	distinct date_format(b.date, '%%Y-%%m') as month
from
	t_bills b
where
	b.fk_user=%d
order by
	month desc 
	";
	const get_traff_bills = "
select
	u.login, b.date, b.amount, b.bytes
from
	t_users u, t_bills b
where
	b.fk_user=u.id and u.id=%d and 
    b.date between '%s' and '%s'
order by
	b.date
	";
	const get_traff_by_period = "
            select
                FROM_UNIXTIME(s.end_time, '%%Y-%%m-%%d') as mday,
                sum(s.bytes_in) as bytes_in
            from
                t_sessions s
            where
                s.end_time between UNIX_TIMESTAMP('%s') and
                    UNIX_TIMESTAMP('%s')
                and s.fk_user=%d
            group by
                mday, s.fk_user
            order by
               mday 
	
	";

	// Users
	const users_get_users = 'select u.id, u.login, u.first_name, u.middle_name, u.last_name 
								from t_users u';
	const users_get_user_info = 'select * from t_users u where u.id=%d';
	const users_get_user_info2 = "select * from t_users u where u.login='%s'";
	const users_get_user_from = 'select * from t_user_from uf where uf.fk_user=%d';
	const users_insert_user = "insert into `t_users` values (null, '%s', '%s', '%s', '%s', '%s', %d, %d, %d, '%s', 1, %d, 0.00)";
	const users_update_user = "
		update 
			`t_users` u 
		set 
			u.first_name='%s', u.middle_name='%s', u.last_name='%s',
			u.is_admin=%d, u.is_blocked=%d, u.change_password=%d, u.auth_type='%s',
			u.fk_tariff=%d
		where
			u.id=%d    
	";
	const check_login = "select count(*) from `t_users` u where u.login='%s'";
	const check_ip = "select * from `t_user_from` uf where uf.ip='%s' and uf.fk_user!=%d";
	const check_ip2 = "select * from `t_user_from` uf where uf.ip='%s' and uf.fk_user=%d";
	const check_ip3 = "select * from `t_user_from` uf where uf.fk_user=%d order by uf.last_update desc";
	const delete_user = "delete from `t_users` where id=%d";
	const change_password = "update `t_users` u set u.password='%s', u.change_password=%d where u.id=%d";
	const get_ppays_by_user = "select * from t_users_pays_periodic t where t.fk_user=%d";
	const get_user_sessions = "select * from t_sessions s where s.fk_user=%d order by s.start_time desc limit %d";
	const get_user_source_record = "select * from t_user_from uf where uf.fk_user=%d";
	const update_user_from = "update t_user_from uf set uf.ip='%s', uf.mac='%s', uf.last_update=%d where uf.fk_user=%d";
	const insert_user_from = "insert into t_user_from values(null, %d, '%s', '%s', %d)";
	const delete_user_from = "delete from t_user_from where id=%d";
	const insert_pay = "insert into t_pays values (null, %d, %.2f, '%s', '%s')";
	const insert_user_pay_periodic = "insert into t_users_pays_periodic values (null, %d, %d)";
	const update_pay = "update t_pays p set p.amount=%.2f, p.note='%s' where p.id=%d";
	const update_user_pay_periodic = "update t_users_pays_periodic set fk_pay_periodic=%d where id=%d";
	const delete_pay = "delete from t_pays where id=%d";
	const delete_user_pay_periodic = "delete from t_users_pays_periodic where id=%d";
	const get_bills = "select sum(b.amount) as amount from t_bills b where b.fk_user=%d and b.date between '%s' and '%s' order by b.date";
//	const get_pays = "select sum(p.amount) as amount from t_pays p where p.fk_user=%d and p.datetime between '%s' and '%s'";
	const get_pays = "select * from t_pays p where p.fk_user=%d and p.datetime between '%s' and '%s' order by p.datetime desc";
	const get_pay = "select * from t_pays p where p.id=%d";
	const get_last_session = "select * from t_sessions us where us.fk_user=%d and us.end_time > %d";
	const get_bills_directions = "
select
	fd.name, td.cost, sum(bd.bytes) as bytes
from
	t_users u, t_bills b, t_bills_directions bd, t_flows_directions fd,
    t_tariffs t, t_tariffs_directions td
where
	b.fk_user=u.id and bd.fk_bill=b.id and bd.fk_flow_direction=fd.id and
    u.fk_tariff=t.id and td.fk_tariff=t.id and 
    td.fk_flow_direction=fd.id and
    u.id=%d and b.date between '%s' and '%s'
group by
	fd.name    
	";

	// Statistics 
	const get_sessions_user = "select 
									s.fk_user, sum(s.bytes_in) as 'bytes_in', sum(s.bytes_out) as 'bytes_out'
								from t_sessions s 
									where s.end_time between %d and %d %s
								group
									by s.fk_user
	";
	const get_sessions_day = "
								select 
									FROM_UNIXTIME(s.end_time, '%%Y-%%m-%%d') as end_time_f,
									sum(s.bytes_in) as 'bytes_in',
									sum(s.bytes_out) as 'bytes_out'
								from 
									t_sessions s 
								where 
									s.end_time between %d and %d %s
								group by
									end_time_f

	";
	const get_traff_by_month = "
            select
                sum(s.bytes_in) as bytes_in, sum(s.bytes_out) as bytes_out
            from
                t_sessions s
            where
                s.end_time between UNIX_TIMESTAMP('%s') and
                    UNIX_TIMESTAMP('%s')
				and s.fk_user=%d
	";
	
	//Tariffs
	const delete_tariff = "delete from t_tariffs where id=%d";
	const get_default_tariff = "select id from t_tariffs t where t.default=1";
	const get_tariff = "select * from t_tariffs t where t.id=%d";
	const get_tariffs = "select * from t_tariffs";
	const get_users_by_tariff = "select u.id from t_users u where u.fk_tariff=%d";
	const insert_tariff = "insert into t_tariffs values (null, '%s', %.2f, '%s', %d, '%s', '%s', %.2f, %d, %d, %d)";
	const update_fk_tariff = "update t_users set fk_tariff=%d where id=%d";
	const update_tariff = "update t_tariffs t set t.name='%s', t.cost=%.2f, t.currency='%s', t.update_time='%s', t.monthly_fee=%.2f, t.prepaid_traffic=%d, t.unlim=%d, t.per_directions=%d, t.default=%d where t.id=%d";
	const update_tariff_default = "update t_tariffs t set t.default=%d where t.id=%d";

	const get_sessions_session = "select * from t_sessions s where s.`end_time` between %d and %d %s";
	const part_user_id = "and s.fk_user=%d";
	const get_flows_directions = "select * from t_flows_directions;";
	const get_flow_direction = "select * from t_flows_directions where id=%d;";
	const update_flow_direction = "update t_flows_directions set flow_srcaddr=%u, flow_dstaddr=%u, flow_srcaddr_mask=%u, flow_dstaddr_mask=%u, name='%s' where id=%d;";
	const insert_flow_direction = "insert into t_flows_directions values (null, %u, %u, %u, %u, '%s');";
	const delete_flow_direction = "delete from t_flows_directions where id=%d;";
	const get_td = "
select
	fd.id, fd.name, td.cost, td.fk_tariff
from 
	t_tariffs_directions td, t_flows_directions fd
where
	td.`fk_flow_direction`=fd.id and
	td.fk_tariff=%d
	";
	const get_td2 = "
select
	td.id, t.name as \"tariff_name\", fd.name as \"fd_name\"
from 
	t_tariffs_directions td, t_tariffs t,
    t_flows_directions fd
where
	td.fk_tariff=t.id and td.fk_flow_direction=fd.id and
	fk_tariff=%d and fk_flow_direction=%d
	";
	const insert_td = "insert into t_tariffs_directions values(null, %d, %d, %.2f)";
	const update_td = "update t_tariffs_directions set cost=%.2f where id=%d";
	const delete_td = "delete from t_tariffs_directions where id=%d";

	//Periodic pays
	const delete_pay_periodic = "delete from t_pays_periodic where id=%d";
	const get_default_fee = "select id from t_pays_periodic t where t.default=1";
	const get_pay_periodic = "select * from t_pays_periodic t where t.id=%d";
	const get_pays_periodic = "select * from t_pays_periodic";
	const insert_pay_periodic = "insert into t_pays_periodic values (null, %.2f, '%s', '%s', '%s')";
	const update_pay_periodic = "update t_pays_periodic p set p.amount=%.2f, p.note='%s' where p.id=%d";

	//Ratelimits
	const delete_ratelimit = "delete from t_ratelimits where id=%d";
	const get_ratelimit = "select * from t_ratelimits where id=%d";
	const get_ratelimits = "select * from t_ratelimits";
	const insert_ratelimit = "insert into t_ratelimits values (null, '%s', %d)";
	const insert_user_ratelimit = "insert into t_users_ratelimits values (null, %d, %d)";
	const update_ratelimit = "update t_ratelimits set name='%s', rate=%d where id=%d";
	const get_ratelimit_by_user = "select * from t_users_ratelimits where fk_user=%d";
	const update_user_ratelimit = "update t_users_ratelimits set fk_ratelimit=%d where id=%d";
	const delete_user_ratelimit = "delete from t_users_ratelimits where id=%d";
	// id, fk_user, action, ip
	const insert_task_ratelimit = "insert into t_tasks_ratelimit values (%d, %d, '%s', '%s')";
	const get_ratelimit_info = "
select
	r.name,r.rate
from
	t_users u, t_ratelimits r, t_users_ratelimits url
where
	url.fk_user=u.id and url.fk_ratelimit=r.id and u.id=%d
	";

	//Firewall
	// id, fk_user, action, ip
	const insert_task_firewall = "insert into t_tasks_firewall values (%d, %d, '%s', '%s')";

	// Options
	const get_options = "select * from t_options";
	const insert_option = "insert into t_options values('%s', '%s')";
	const update_option = "update t_options set option_value='%s' where option_name='%s'";
	const select_option_count = "select count(*) from t_options where option_name='%s'";

	// Session
	const session_get_sessions = "select * from t_sessions s where s.`end_time`>=%d and s.fk_user=%d";
	const update_session = "update t_sessions s set s.start_time=%d, s.end_time=%d where s.id=%d";
	const insert_session = "insert into t_sessions values (null, %d, %d, %d, %d, %d, '%s')";
	const get_user_source = "select uf.ip from t_users u, t_user_from uf where uf.fk_user=u.id and u.login='%s' and uf.ip='%s'";

	//Bills
	const get_fees_by_user_id = "select sum(amount) from t_fees where fk_user=%d and date='%s';";
	const get_fees_by_user_id2 = "
select
	f.date,f.amount,ft.note
from
  t_fees f, t_fees_types ft
where
	f.fk_fee_type=ft.id and
  f.fk_user = %d and
  f.date = '%s';
	
	";
}

?>
